create or replace type body pluto.test_attendance is
/*
    Copyright 2009 Highline Public Schools
--
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
--
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
--
    You should have received a copy of the GNU General Public License
    along with This program.  If not, see <http://www.gnu.org/licenses/>.
*/

member procedure startup_student_sample is
   --Build a random set of students.
   l_sample_size number(9);
   l_sample_verify number(9);
   l_exc_sample_size exception;
begin
   l_sample_size := 100;
   if l_sample_size is null then
      raise l_exc_sample_size;
   end if;
   delete from admassist.cust_hsd_student_sample;
   insert into admassist.cust_hsd_student_sample (pupil_number)
   select pupil_number from
   (
      select s.pupil_number
      from admassist.students s
      where
         1=1
         --and s.pupil_number = 000
         --and s.grade between '07' and '12'
         and nvl(s.withdraw_date,sysdate) > (sysdate - to_yminterval('00-04'))
         and nvl(s.withdraw_date,sysdate) > admassist.aa$sis$get_school.YEAR_START_DATE(s.school, admassist.aa$sis$get_school.SCHOOL_YEAR(s.school,sysdate))
      order by ora_hash(s.pupil_number,4000000000,ora_hash(sysdate))
   )
   where rownum <= l_sample_size
   ;
   select count(*) into l_sample_verify from admassist.cust_hsd_student_sample;
   m_util_object.log(message => 'Sample size = ' || to_char(l_sample_verify));
exception
   when l_exc_sample_size then
      dbms_output.put_line('Invalid student sample size given:  ' || nvl(to_char(l_sample_size),'null'));
      raise l_exc_sample_size;
end;

member procedure test_code_sub_allocation_code is
   l_count number(9);
begin
   select count(*) into l_count
   from admassist.attendance_codes t
   where
      t.authorized = 'N'
      and t.sub_allocation_code not in ('EXAB','UNEXAB')
      ;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 0,
      test_label     => 'sub_allocation_code exists for all unauthorized'
   );
end;
   
member procedure test_code_authorized is
   l_count number(9);
begin
   select count(*) into l_count
   from admassist.attendance_codes ac
   where 
      ac.authorized = 'Y'
      and (admassist.cust_hsd_attendance.absence_type('A',ac.attendance_code,ac.school) <> 'A'
      or admassist.cust_hsd_attendance.absence_type('L',ac.attendance_code,ac.school) <> 'A')
      ;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 0,
      test_label     => 'Authorized = A'
   );
end ;
    
member procedure test_code_excused is
   l_count number(9);
begin
   select count(*) into l_count
   from admassist.attendance_codes ac
   where 
      ac.attendance_desc = 'Excused'
      and (admassist.cust_hsd_attendance.absence_type('A',ac.attendance_code,ac.school) <> 'E'
      or admassist.cust_hsd_attendance.absence_type('L',ac.attendance_code,ac.school) <> 'E')
      ;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 0,
      test_label     => 'Excused = E'
   );
end ;
   
member procedure test_code_unexcused is
   l_count number(9);
begin
   select count(*) into l_count
   from admassist.attendance_codes ac
   where 
      ac.attendance_desc = 'Unexcused'
      and (admassist.cust_hsd_attendance.absence_type('A',ac.attendance_code,ac.school) <> 'U'
      or admassist.cust_hsd_attendance.absence_type('L',ac.attendance_code,ac.school) <> 'U')
      ;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 0,
      test_label     => 'Unexcused = U'
   );
end;

member procedure test_code_null is
   l_code varchar2(1);
begin
   select admassist.cust_hsd_attendance.absence_type('A',null,1) into l_code from dual
      ;
   m_util_object.is_ok(
      data_got        => l_code,
      data_expected   => 'U',
      test_label     => 'Null reason_code = U'
   );
end;

member procedure test_sec_att_day_percent_100 is
   l_sample number(9); --subset of stu to query
   l_count number(9);
   l_percent number(8,1);
   l_passed boolean;
   l_msg varchar2(1000);
begin
   select count(*) into l_sample 
   from
   (
      select distinct a.pupil_number, a.absent_date
      from admassist.sec_attendances a
      where a.pupil_number in (select pupil_number from admassist.cust_hsd_student_sample)
   ) sub
   ;
   select count(*) into l_count from
   (
      select
         days.pupil_number,
         days.absent_date
         --sum(admassist.cust_hsd_attendance.sec_att_day_percent(days.pupil_number,cbo.absent_code,cbo.absence_type,days.absent_date)) as tot_pct
      from 
         (
            select distinct 
               attp.pupil_number,
               attp.absent_date
            from admassist.sec_attendances attp
            where 
               --skip SASI imported data and any shortly after:
               absent_date > '4/1/2009'
               and absent_date > (sysdate - to_yminterval('01-00'))
               and attp.pupil_number in (select pupil_number from admassist.cust_hsd_student_sample)
         ) days
         cross join 
         (
            select absent_codes.absent_code, absence_types.absence_type
            from
            (
               select 'A' absent_code from dual
               union all
               select 'L' absent_code from dual
            ) absent_codes
            cross join
            (
               select 'U' absence_type from dual
               union all
               select 'E' absence_type from dual
               union all
               select 'A' absence_type from dual
            ) absence_types
         ) cbo
      group by 
         days.pupil_number,
         days.absent_date
      having sum(admassist.cust_hsd_attendance.sec_att_day_percent(days.pupil_number,cbo.absent_code,cbo.absence_type,days.absent_date)) > 100
   ) sub1
   ;
   l_percent := 100.0 - ((l_count/l_sample) * 100);
   l_msg := 'Total percentage minutes absence per day <= 100% (minding <1% eSIS bugs). Percent of compliant student * days: ' || to_char(l_percent,'999.9');
   if (l_percent >= 99.0) then
      l_passed := true;
   else
      l_passed := false;
   end if;
   m_util_object.ok(
      test_passed        => l_passed,
      test_label     => l_msg
   );
end ;

member procedure test_sec_att_half_excused is
   l_pupil_number number(9);
   l_absent_date date;
   l_expected number(1);
   l_count number(9);
begin
   l_pupil_number := null;
   select att.pupil_number, att.absent_date
   into l_pupil_number, l_absent_date
   from admassist.sec_attendances att
   where 
      admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','U',att.absent_date) >= 25
      and admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','U',att.absent_date) = 
         admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','E',att.absent_date)
      and rownum = 1
   ;
   if l_pupil_number is null then
      l_expected := 0;
   else
      l_expected := 1;
      select admassist.cust_hsd_attendance.sec_att_day_range_count(l_pupil_number,'U',l_absent_date,l_absent_date) into l_count from dual;
   end if;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => l_expected,
      test_label     => 'Secondary day half excused, half unexcused => unexcused'
   );
end;

member procedure test_sec_abs_per_day is
   l_result number(1);
   l_passed boolean;
begin
   select 
      case when exists
         (   
            select 
               atte.pupil_number,
               atte.absent_date
            from admassist.sec_attendances atte
            where 
               1=1
               and absent_date > (sysdate - to_yminterval('01-00'))
               and atte.pupil_number in (select pupil_number from admassist.cust_hsd_student_sample)
               and 
                  admassist.cust_hsd_attendance.sec_att_day_range_count(atte.pupil_number,'E',atte.absent_date,atte.absent_date) +
                  admassist.cust_hsd_attendance.sec_att_day_range_count(atte.pupil_number,'U',atte.absent_date,atte.absent_date) +
                  admassist.cust_hsd_attendance.sec_att_day_range_count(atte.pupil_number,'A',atte.absent_date,atte.absent_date) 
                     > 1
            group by
               atte.pupil_number,
               atte.absent_date
         ) then 1
         else 0
      end into l_result from dual
      ;
      if l_result = 0 then
         l_passed := true;
      else
         l_passed := false;
      end if;
   m_util_object.ok(
      test_passed    => l_passed,
      test_label     => 'Maximum daily secondary absences per day is one'
   );
end;

member procedure test_sec_att_combined_types is
   l_pupil_number number(9);
   l_absent_date date;
   l_count number(9);
begin
   select att.pupil_number, att.absent_date
   into l_pupil_number, l_absent_date
   from admassist.sec_attendances att
   where 
      admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','U',att.absent_date) < 50
      and admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','E',att.absent_date) < 50
      and admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','A',att.absent_date) < 50
      and admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','ANY',att.absent_date) >= 50
      and admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','E',att.absent_date) >
         (admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','U',att.absent_date) +
            admassist.cust_hsd_attendance.sec_att_day_percent(att.pupil_number,'A','A',att.absent_date))
      and rownum = 1
   ;
   select admassist.cust_hsd_attendance.sec_att_day_range_count(l_pupil_number,'E',l_absent_date,l_absent_date) into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 1,
      test_label     => 'Secondary individual absence types below threshhold but combined are over threshhold'
   );
end;

member procedure test_sum_of_types is
   l_count number;
begin
   select count(*) into l_count
   from admassist.cust_hsd_student_sample smpl
   where
      admassist.cust_hsd_attendance.stu_att_day_range_count(smpl.pupil_number, 'ANY', (sysdate - to_yminterval('01-00')),sysdate)
      <>
      (
         admassist.cust_hsd_attendance.stu_att_day_range_count(smpl.pupil_number, 'U', (sysdate - to_yminterval('01-00')),sysdate)
         + admassist.cust_hsd_attendance.stu_att_day_range_count(smpl.pupil_number, 'E', (sysdate - to_yminterval('01-00')),sysdate)
         + admassist.cust_hsd_attendance.stu_att_day_range_count(smpl.pupil_number, 'A', (sysdate - to_yminterval('01-00')),sysdate)
      );
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 0,
      test_label     => 'Sum of absence types = sum of "ANY" types'
   );
end;

member procedure test_sec_att_day_range_count1 is
   l_count number(9);
   l_pupil number(9);
begin
   l_pupil := 0;
   select admassist.cust_hsd_attendance.stu_att_day_range_count(l_pupil, 'U', '9/4/2008','3/31/2009') into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 61,
      test_label     => 'Unexcused total for secondary student ' || to_char(l_pupil)
   );
   select admassist.cust_hsd_attendance.stu_att_day_range_count(l_pupil, 'E', '9/4/2008','3/31/2009') into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 7,
      test_label     => 'Excused total for secondary student ' || to_char(l_pupil)
   );
   select admassist.cust_hsd_attendance.stu_att_day_range_count(l_pupil, 'A', '9/4/2008','3/31/2009') into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 1,
      test_label     => 'Authorized total for secondary student ' || to_char(l_pupil)
   );
end ;

member procedure test_elem_att_day_range_count1 is
   l_count number(9);
   l_pupil number(9);
begin
   l_pupil := 0;
   select admassist.cust_hsd_attendance.stu_att_day_range_count(l_pupil, 'U', '9/4/2008','3/31/2009') into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 1,
      test_label     => 'Unexcused total for elementary student ' || to_char(l_pupil)
   );
   select admassist.cust_hsd_attendance.stu_att_day_range_count(l_pupil, 'E', '9/4/2008','3/31/2009') into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 13,
      test_label     => 'Excused total for elementary student ' || to_char(l_pupil)
   );
   select admassist.cust_hsd_attendance.stu_att_day_range_count(l_pupil, 'A', '9/4/2008','3/31/2009') into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 0,
      test_label     => 'Authorized total for elementary student ' || to_char(l_pupil)
   );
end ;

member procedure test_elem_ft_abs_am_only is
   l_pupil_number number(9);
   l_absent_date date;
   l_count number(9);
begin
   select atte.pupil_number, atte.absent_date
   into l_pupil_number, l_absent_date
   from admassist.elem_attendances atte
   where 
      atte.absent_code = 'A'
      and atte.absent_code_pm is null
      and exists (select 1 from admassist.cust_hsd_student_sample smp where smp.pupil_number = atte.pupil_number)
      and rownum = 1
   ;
   select admassist.cust_hsd_attendance.elem_att_day_range_count(l_pupil_number,'ANY',l_absent_date,l_absent_date) into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 1,
      test_label     => 'Elem stu absent AM only is absent'
   );
end;

member procedure test_elem_abs_pm_only is
   l_pupil_number number(9);
   l_absent_date date;
   l_count number(9);
begin
   select atte.pupil_number, atte.absent_date
   into l_pupil_number, l_absent_date
   from admassist.elem_attendances atte
   where 
      atte.absent_code is null
      and atte.absent_code_pm = 'A'
      and exists (select 1 from admassist.cust_hsd_student_sample smp where smp.pupil_number = atte.pupil_number)
      and rownum = 1
   ;
   select admassist.cust_hsd_attendance.elem_att_day_range_count(l_pupil_number,'ANY',l_absent_date,l_absent_date) into l_count from dual;
   m_util_object.is_ok(
      data_got        => l_count,
      data_expected   => 1,
      test_label     => 'Elem stu absent PM only is absent'
   );
end;

member procedure test_elem_U_override_E is
   l_count1 number(9);
   l_count2 number(9);
   l_passed boolean;
begin
   l_passed := false;
   select 
      count(atte.absent_date),
      nvl(sum(admassist.cust_hsd_attendance.elem_att_day_range_count(atte.pupil_number,'U',atte.absent_date,atte.absent_date)),0)
   into l_count1, l_count2
   from 
      admassist.elem_attendances atte
   where 1=1
      and atte.pupil_number in (select pupil_number from admassist.cust_hsd_student_sample)
      and atte.absent_code = 'A'
      and atte.absent_code_pm = 'A'
      and 
      (
         (
            admassist.cust_hsd_attendance.absence_type(absent_code,atte.reason_code_am,atte.school) = 'E'
            and admassist.cust_hsd_attendance.absence_type(absent_code_pm,atte.reason_code_pm,atte.school) = 'U'
         )
         OR
         (
            admassist.cust_hsd_attendance.absence_type(absent_code,atte.reason_code_am,atte.school) = 'U'
            and admassist.cust_hsd_attendance.absence_type(absent_code_pm,atte.reason_code_pm,atte.school) = 'E'
         )
      )
   ;
   if l_count1 = l_count2 then
      l_passed := true;
   else
      l_passed := false;
   end if;
   m_util_object.ok(
      test_passed    => l_passed,
      test_label     => 'Full-time elem unexcused overrides excused on same day'
   );
end;

member procedure test_elem_abs_per_day is
   l_result number(1);
   l_passed boolean;
begin
   select 
      case when exists
         (   
            select 
               atte.pupil_number,
               atte.absent_date
            from admassist.elem_attendances atte
            where 
               1=1
               and absent_date > (sysdate - to_yminterval('01-00'))
               and atte.pupil_number in (select pupil_number from admassist.cust_hsd_student_sample)
               and 
                  admassist.cust_hsd_attendance.elem_att_day_range_count(atte.pupil_number,'E',atte.absent_date,atte.absent_date) +
                  admassist.cust_hsd_attendance.elem_att_day_range_count(atte.pupil_number,'U',atte.absent_date,atte.absent_date) +
                  admassist.cust_hsd_attendance.elem_att_day_range_count(atte.pupil_number,'A',atte.absent_date,atte.absent_date) 
                     > 1
            group by
               atte.pupil_number,
               atte.absent_date
         ) then 1
         else 0
      end into l_result from dual
      ;
      if l_result = 0 then
         l_passed := true;
      else
         l_passed := false;
      end if;
   m_util_object.ok(
      test_passed    => l_passed,
      test_label     => 'Maximum daily elem absences per day is one'
   );
end;

member procedure test_daily_period_in_same_day is
   --l_result number(1);
   l_passed boolean;
begin
   l_passed := false;
   m_util_object.ok(
      test_passed    => l_passed,
      test_label     => 'Students with daily and period absences on the same day (known issue)'
   );
end;

member procedure shutdown_zz_rollback is
begin
   rollback;
end;

constructor function test_attendance
   return self as result is
begin
   m_util_object := pluto.pluto_util_obj( output_object => pluto.pluto_output_obj( ));
   --m_sample_size := sample_size;
   --dbms_output.put_line(nvl(to_char(m_sample_size),'null'));
   return;
end;

end; --type body

/*
declare
   ut_obj  pluto.test_attendance := pluto.test_attendance( );
begin
   ut_obj.run_tests;
end;
*/
/
